package MySql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class UserManager {
    //返回表中所有记录


    public static void displayAllRows() throws SQLException{
        String sql="SELECT * FROM usermember";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数
            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
            }else {
                rs.beforeFirst(); //指针到第一条记录之前
                StringBuilder buffer=new StringBuilder(); //动态字符串
                while (rs.next()) { //遍历记录集
                    buffer.append(rs.getString("username")).append(",");
                    buffer.append(rs.getString("passwd")).append(",");
                    buffer.append(rs.getString("email")).append(",");
                    buffer.append(rs.getString("truename")).append(",");
                    buffer.append(rs.getInt("age")).append(",");
                }//end while
                System.out.println(buffer.toString());
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end displayAllRows

    public static ArrayList<User> displayAllUser() throws SQLException {
        String sql="SELECT * FROM usermember";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数

            System.out.println(nRows);

            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
                return null;
            }else {
                ArrayList<User> users=new ArrayList<User>();
                rs.beforeFirst(); //指针到第一条记录之前
                while (rs.next()) { //遍历记录集
                    String id=rs.getString("username");
                    String kind=rs.getString("passwd");
                    String title = rs.getString("email");
                    String brief = rs.getString("truename");
                    Integer age = rs.getInt("age");
                    User user=new User(id,kind,title,brief,age);
                    users.add(user);
                }//end while
//                System.out.println(buffer.toString());
                return users;
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
        return null;
    }//end displayAllRows

    //根据id查找记录
    public static User getRowById(String username) throws SQLException {
        String sql="SELECT * FROM usermember WHERE username=?";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            st.setString(1, username); //设置参数
            rs=st.executeQuery(); //返回结果集
            if (rs.next()) { //找到
                User bean=new User(); //定义用户数据实体
                bean.setUsername(rs.getString("username"));
                bean.setPasswd(rs.getString("passwd"));
                bean.setEmail(rs.getString("email"));
                bean.setTrueName(rs.getString("truename"));
                bean.setAge(rs.getInt("age"));
                return bean; //返回用户数据实体
            }else { //没找到
                return null;
            }
        } catch (SQLException ex) {
            return null;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end getRowById
    //注册用户
    public static boolean registerUser(User bean) throws SQLException{
        if (getRowById(bean.getUsername())!=null) return false;//如果用户存在，则注册失败
        String sql="INSERT INTO usermember (username,passwd,email,truename,age) VALUES (?,?,?,?,?)";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            st.setString(1,bean.getUsername());
            st.setString(2, bean.getPasswd());
            st.setString(3, bean.getEmail());
            st.setString(4,bean.getTrueName());
            st.setInt(5, bean.getAge());
            int affected=st.executeUpdate();
            return affected==1; //注册成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
        }//end try
    }//end registerUser
    //用户登录
    //对比账号和答案1
    public static boolean passfind(User bean) throws SQLException{
        String sql="SELECT * FROM usermember WHERE username=? AND email=? AND truename=?";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUsername());
            st.setString(2, bean.getEmail());
            st.setString(3, bean.getTrueName());
            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    public static boolean testId(User bean) throws SQLException{
        String sql="SELECT * FROM usermember WHERE username=? ";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUsername());

            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    public static boolean userLogin(User bean) throws SQLException {
        String sql="SELECT * FROM usermember WHERE username=? AND passwd=?";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUsername());
            st.setString(2, bean.getPasswd());
            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end userLogin
    //修改用户
    public static boolean updateUser(User bean) {
        String sql="UPDATE usermember SET username=? , passwd= ? ,"
                + " email=? , truename= ? , age= ? WHERE username=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            st.setString(1, bean.getUsername());
            st.setString(2, bean.getPasswd());
            st.setString(3, bean.getEmail());
            st.setString(4,bean.getTrueName());
            st.setInt(5,bean.getAge());
            st.setString(6, bean.getUsername());
            int affected=st.executeUpdate();
            return affected==1; //修改成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
    //删除用户
    public static boolean deleteUser(String userId) {
        String sql="DELETE FROM usermember WHERE username=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            st.setString(1,userId);
            int affected=st.executeUpdate();
            return affected==1; //删除成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
}//end class